package vell.velllock.database;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import vell.velllock.object.Word;
import vell.velllock.object.WordDBInfo;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class WordDBOperator {
	private Context mContext;
	private SQLiteDatabase wordDB;
	private int ShowWordNum = 2;
	public WordDBOperator(Context context, File DBFile){
		this.mContext = context;
		wordDB = SQLiteDatabase.openDatabase(DBFile.getAbsolutePath(), null, SQLiteDatabase.OPEN_READWRITE);
	}
	
	public WordDBInfo getWordDBInfo() throws Exception {
		String sql = "select * from WordInfo";
		Cursor cursor = wordDB.rawQuery(sql, null);
		if(cursor.moveToFirst() == false){
			throw new Exception("WordDB is bad");
		}
		WordDBInfo WDI = new WordDBInfo();
		WDI.setDBName(cursor.getString(0));
		WDI.setInfo(cursor.getString(1));
		WDI.setWordNum((int)getCountOfWordDB());
		WDI.setActive(0);
		return WDI;
	}
	
	public long getCountOfWordDB(){
		SQLiteStatement statement = wordDB.compileStatement("SELECT COUNT(*) FROM word");
		return statement.simpleQueryForLong();
	}
	
	public Word[] getShowWords(){
		Word[] words = new Word[ShowWordNum];
		words[0] = getRandomWord();
		words[1] = getOldWord(new int[]{words[0].getId()});
		return words;
	}
	
	public Word getOldWord(int[] excludeIds){
		String sql = "select * from word where ";
		for(int i=0; i<excludeIds.length; i++){
			sql += "id <> " + excludeIds[i];
			if(i+1 < excludeIds.length)
				sql += " and ";
		}
		sql += " ORDER BY remember desc LIMIT 1 ";
		Log.i("sql", sql);
		Cursor cursor = wordDB.rawQuery(sql, null);
		List<Word> wordList = cursorToList(cursor);
		if(wordList == null) return null;
		else return wordList.get(0);
	}
	
	public Word getRandomWord(){
		int randNum = (int)(getCountOfWordDB() * Math.random());
		String sql = "select * from word where id = " + randNum;
		Cursor cursor = wordDB.rawQuery(sql, null);
		List<Word> wordList = cursorToList(cursor);
		if(wordList == null) return null;
		else return wordList.get(0);
	}
	
	private List<Word> cursorToList(Cursor cursor){
		List<Word> wordList = new ArrayList<Word>();
		if(cursor.moveToFirst() == false) return null;
		do{
			Word word = new Word();
			word.setId(cursor.getInt(0));
			word.setWord(cursor.getString(1));
			word.setInterpretation(cursor.getString(2));
			word.setUsageCN(cursor.getString(3));
			word.setUsageEN(cursor.getString(4));
			word.setSoundmark(cursor.getString(5));
			word.setWrongNum(cursor.getInt(6));
			word.setRightNum(cursor.getInt(7));
			word.setRemember(cursor.getInt(8));
			wordList.add(word);
		}while(cursor.moveToNext());
		
		return wordList;
	}
}
